package cardval.dao;

import cardval.entity.PartnerInfo;
import cardval.entity.PartnerOverview;
import cardval.entity.PartnerTotalOverview;
import cardval.entity.PartnerTrans;
import cardval.util.DateUtil;
import cardval.util.StringUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author Renne66  465119455@qq.com
 * @ClassName: PartnerDao
 * @Description: TODO
 * @date 2016年4月29日 上午10:23:53
 */
@Repository("partnerDao")
public class PartnerDao {
    private static final Logger logger = Logger.getLogger(PartnerDao.class);
    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 添加合作商伙伴
     *
     * @param partner
     * @return
     */
    public int addPartner(final PartnerInfo partner) {
        final String sql = "INSERT INTO partner_info (PARTNER_NO_,PARTNER_NAME_,PARTNER_MONEY_, PARTNER_KEY, contact, mobile, password) VALUES (?,?,?,?,?,?, '123456')";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                PreparedStatement ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
                ps.setString(1, partner.getPARTNER_NO_());
                ps.setString(2, partner.getPARTNER_NAME_());
                ps.setInt(3, partner.getPARTNER_MONEY_());
                ps.setString(4, partner.getPARTNER_KEY());
                ps.setString(5, partner.getContact());
                ps.setString(6, partner.getMobiel());
                return ps;
            }
        }, keyHolder);
        logger.info("addPartner:" + sql);
        return keyHolder.getKey().intValue();
    }

    /**
     * 查询合作商伙伴
     *
     * @param PARTNER_NO_ 合作商编号
     * @param offset
     * @param limit       @return
     */
    public List<PartnerInfo> queryPartners(String PARTNER_NO_, int offset, int limit) {
        String sql = "SELECT * FROM partner_info";
        if (StringUtil.isNotEmpty(PARTNER_NO_)) {
            sql += " WHERE PARTNER_NO_ = '" + PARTNER_NO_ + "'";
        }
        sql += " limit " + offset + "," + limit;
        logger.info("queryAdmin:" + sql);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PartnerInfo.class));
    }

    /**
     * 查询合作商伙伴
     *
     * @param PARTNER_NO_ 合作商编号
     */
    public List<PartnerInfo> queryPartners(String PARTNER_NO_) {
        String sql = "SELECT * FROM partner_info";
        if (StringUtil.isNotEmpty(PARTNER_NO_)) {
            sql += " WHERE PARTNER_NO_ = '" + PARTNER_NO_ + "'";
        }
        logger.info("queryAdmin:" + sql);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PartnerInfo.class));
    }

    public long queryPtrCount(String PARTNER_NO_) {
        String sql = "SELECT count(1) FROM partner_info";
        if (StringUtils.isNotBlank(PARTNER_NO_)) {
            sql += " where PARTNER_NO_ = '" + PARTNER_NO_ + "'";
        }
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return (long) list.get(0).get("count(1)");
    }
    
   /**
    * 20171011--为开放商户查询更改密码操作
    * 更新合作商密码
    * @param password 商户密码
    * @param PARTNER_NO_  商户号
    * @return
    */
    public int updatePartnerPassword(String password, String PARTNER_NO_ ) {
    	String sql = "UPDATE partner_info SET password = ? WHERE PARTNER_NO_ = ?";
    	List<Object> list = new ArrayList<Object>();
    	list.add(password);
    	list.add(PARTNER_NO_);
    	logger.info("updatePartnerPassword:"+sql);
    	logger.info("updatePartnerPassword:" + list.toString());
		return jdbcTemplate.update(sql,list.toArray());    	
    }

    /**
     * 更新合作商余额
     *
     * @param PARTNER_NO_    合作商ID
     * @param PARTNER_MONEY_ 合作商余额
     * @return
     */
    public int updatePartnersMoney(String PARTNER_NO_,  int money, int transType ) {
    	String sql = "";
    	//判断交易类型 为2 进行相减操作 否则进行相加操作
    	if(transType == 2) {   		
    		 sql = "UPDATE partner_info SET PARTNER_MONEY_ = PARTNER_MONEY_ - ? WHERE PARTNER_NO_ = ?";
    	}else {
    		 sql = "UPDATE partner_info SET PARTNER_MONEY_ = PARTNER_MONEY_ + ? WHERE PARTNER_NO_ = ?";
    	}
        List<Object> list = new ArrayList<Object>();
        list.add(money);
        list.add(PARTNER_NO_);
        logger.info("updatePartnersMoney:" + sql);
        logger.info("updatePartnersMoney:" + list.toString());
        return jdbcTemplate.update(sql, list.toArray());
    }

    /**
     * 添加合作商交易信息
     *
     * @return
     */
    public int addPartnerTrans(PartnerTrans partnerTrans) {
        String sql = "INSERT INTO partner_trans (PARTNER_ID_,TRANS_TYPE_,TRANS_MONEY_,TRANS_DATE_, sdate, partner_no) VALUES (?,?,?,?,?,?)";
        List<Object> list = new ArrayList<>();
        list.add(partnerTrans.getPARTNER_ID_());
        list.add(partnerTrans.getTRANS_TYPE_());
        list.add(partnerTrans.getTRANS_MONEY_());
        list.add(StringUtil.isEmptyReplace(partnerTrans.getTRANS_DATE_(), DateUtil.getCurDate()));
        list.add(new SimpleDateFormat("yyyyMMdd").format(new Date()));
        list.add(partnerTrans.getPartnerNo());
        logger.info("addPartnerTrans:" + sql);
        logger.info("addPartnerTrans:" + list.toString());
        return jdbcTemplate.update(sql, list.toArray());
    }

    /**
     * 查询合作商交易信息
     *
     * @param partner_no  partnerno
     * @param TRANS_TYPE_ 交易类型  1 充值 2 消费
     * @param partner_no  partner_no
     * @param TRANS_TYPE_ @return
     * @param offset      limit(offset, limit)
     * @param limit       limit(offset, limit)
     */
    public List<PartnerTrans> queryPartnerTrans(String partner_no, String TRANS_TYPE_, Integer offset, Integer limit) {
        String sql = "SELECT * FROM partner_trans WHERE 1 = 1";
        if (StringUtils.isNotBlank(partner_no)) {
            sql += " AND partner_no = '" + partner_no + "'";
        }
        if (StringUtils.isNotBlank(TRANS_TYPE_)) {
            sql += " AND TRANS_TYPE_ = " + TRANS_TYPE_ + "";
        }
        sql += " ORDER BY TRANS_DATE_ DESC LIMIT " + offset + "," + limit;
        logger.info("queryPartnerTrans:" + sql);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PartnerTrans.class));
    }

    public long queryPtrTransCount(String partner_no, String TRANS_TYPE_) {
        String sql = "select count(1) from partner_trans WHERE 1 = 1";
        if (StringUtils.isNotBlank(partner_no)) {
            sql += " AND partner_no = '" + partner_no + "'";
        }
        if (StringUtils.isNotBlank(TRANS_TYPE_)) {
            sql += " AND TRANS_TYPE_ = " + TRANS_TYPE_ + "";
        }

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return (long) list.get(0).get("count(1)");
    }
    
    //新增日期查询
    public List<PartnerOverview> queryPtrOverview(String dateStrat, String dateEnd, String partner_no, int offset, int limit) {
    	//新增两个字段作为结果统计
        String sql = "SELECT partner_no as partnerNo,sdate as date,TRANS_TYPE_ as type, count(0) as count, sum(TRANS_MONEY_) as sumAmt FROM partner_trans WHERE 1 = 1 ";
        String end = "GROUP BY TRANS_TYPE_,partner_no, sdate ORDER BY sdate DESC";
        if (StringUtils.isNotBlank(partner_no)) {
            sql += " and partner_no = '" + partner_no + "' ";
        }
        if (StringUtils.isNotBlank(dateStrat) && StringUtils.isNotBlank(dateEnd)) {
            sql += " and sdate between '" + dateStrat + "' and '"+ dateEnd +"' ";
        }
        
        sql += end + " limit " + offset + ", " + limit;
        List<PartnerOverview> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PartnerOverview.class));
        //新增一行作为统计交易数和金额的总数 0925
        String sqlTol = "SELECT TRANS_TYPE_ as type, count(0) as count, SUM(TRANS_MONEY_) AS sumAmt FROM partner_trans where 1=1";
        String endTol =	 "GROUP BY TRANS_TYPE_";
        if (StringUtils.isNotBlank(partner_no)) {
        	sqlTol += " and partner_no = '" + partner_no + "' ";
        }
        if (StringUtils.isNotBlank(dateStrat) && StringUtils.isNotBlank(dateEnd)) {
        	sqlTol += " and sdate between '" + dateStrat + "' and '"+ dateEnd +"' ";
        }
        sqlTol += endTol;
        
        //查询一行数据，即一条记录，一条记录包含多个字段, 使用返回的列做key。
        List<PartnerOverview> s = this.jdbcTemplate.query(sqlTol, new BeanPropertyRowMapper<>(PartnerOverview.class)); 
        //判断是否有充值的操作交易，有的话查出来是第一天条
        PartnerOverview p = new PartnerOverview();
        if(s.get(0).getType()==1) {     	
        	 p.setDate("总计");
             p.setPartnerNo("--");
         	p.setStype("消费");
        	long count = s.get(1).getCount();
        	logger.info("输出的消费笔数："+count);
        	p.setCount(count);
        	p.setSumAmt(s.get(1).getSumAmt() * -1);
        	
        } if(s.get(0).getType()==2){
        p.setDate("总计");
        p.setPartnerNo("--");
    	p.setStype("消费");
    	p.setCount(s.get(0).getCount());
    	p.setSumAmt(s.get(0).getSumAmt() * -1);
        }
        list.add(p);
        return list;
    }

    public long getPtrOVCount(String dateStrat, String dateEnd, String ptrNo) {
        String sql = "SELECT COUNT(1) FROM (SELECT t.partner_no AS partnerNo, t.sdate AS date, t.TRANS_TYPE_ AS type, COUNT(0) AS count, SUM(t.TRANS_MONEY_) AS sumAmt FROM partner_trans t where 1=1";
        if (StringUtils.isNotBlank(ptrNo)) {
            sql += " and t.partner_no = '" + ptrNo + "'";
        }
        if (StringUtils.isNotBlank(dateStrat) && StringUtils.isNotBlank(dateEnd)) {
            sql += " and sdate between '" + dateStrat + "' and '"+ dateEnd +"' ";
        }
        sql += " GROUP BY TRANS_TYPE_ , partner_no,sdate) a";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return (long) list.get(0).get("count(1)");
    }
    
    
    
    /**
     * 查询合作商所有的交易信息
     * 20170913
     * @param partner_no  partnerno
     * @param TRANS_TYPE_ 交易类型  1 充值 2 消费
     * @param partner_no  partner_no
     * @param TRANS_TYPE_ @return
     * @param offset      limit(offset, limit)
     * @param limit       limit(offset, limit)
     */
    //新增日期查询
    public List<PartnerTotalOverview> queryPtrTotalOverview(String dateStrat, String dateEnd, String partner_no, int offset, int limit) {
    	//新增两个字段作为结果统计
        String sql = "SELECT partner_no as partnerNo,sdate as date,TRANS_TYPE_ as type, count(0) as count, sum(TRANS_MONEY_) as sumAmt FROM partner_trans WHERE 1 = 1 ";
        String end = "GROUP BY partner_no,TRANS_TYPE_";
        if (StringUtils.isNotBlank(partner_no)) {
            sql += " and partner_no = '" + partner_no + "' ";
        }
        if (StringUtils.isNotBlank(dateStrat) && StringUtils.isNotBlank(dateEnd)) {
            sql += " and sdate between '" + dateStrat + "' and '"+ dateEnd +"' ";
        }
        
        sql += end + " limit " + offset + ", " + limit;
        List<PartnerTotalOverview> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PartnerTotalOverview.class));
      //新增一行作为统计交易数和金额的总数 0925
        String sqlTol = "SELECT TRANS_TYPE_ as type, count(0) as count, SUM(TRANS_MONEY_) AS sumAmt FROM partner_trans where 1=1";
        String endTol =	 "GROUP BY TRANS_TYPE_";
        if (StringUtils.isNotBlank(partner_no)) {
        	sqlTol += " and partner_no = '" + partner_no + "' ";
        }
        if (StringUtils.isNotBlank(dateStrat) && StringUtils.isNotBlank(dateEnd)) {
        	sqlTol += " and sdate between '" + dateStrat + "' and '"+ dateEnd +"' ";
        }
        sqlTol += endTol;
        
        //查询一行数据，即一条记录，一条记录包含多个字段, 使用返回的列做key。
        List<PartnerTotalOverview> s = this.jdbcTemplate.query(sqlTol, new BeanPropertyRowMapper<>(PartnerTotalOverview.class)); 
        //判断是否有充值的操作交易，有的话查出来是第一天条
        PartnerTotalOverview p = new PartnerTotalOverview();
        if(s.get(0).getType()==1) {     	
             p.setPartnerNo("总计");
         	p.setStype("消费");
        	long count = s.get(1).getCount();
        	p.setCount(count);
        	p.setSumAmt(s.get(1).getSumAmt()*-1);
        	
        } else{
        p.setPartnerNo("总计");
    	p.setStype("消费");
    	p.setCount(s.get(0).getCount());
    	p.setSumAmt(s.get(0).getSumAmt() * -1);
        }
        list.add(p);
        return list;
    }

    public long getPtrTotalOVCount(String dateStrat, String dateEnd, String ptrNo) {
        String sql = "SELECT COUNT(1) FROM (SELECT t.partner_no AS partnerNo, t.sdate AS date, t.TRANS_TYPE_ AS type, COUNT(0) AS count, SUM(t.TRANS_MONEY_) AS sumAmt FROM partner_trans t where 1=1";
        if (StringUtils.isNotBlank(ptrNo)) {
            sql += " and t.partner_no = '" + ptrNo + "'";
        }
        if (StringUtils.isNotBlank(dateStrat) && StringUtils.isNotBlank(dateEnd)) {
            sql += " and sdate between '" + dateStrat + "' and '"+ dateEnd +"' ";
        }
        sql += " GROUP BY partner_no,TRANS_TYPE_ ) a";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return (long) list.get(0).get("count(1)");
    }
    

}
